# 业务数据库 -> ODS -> DWD -> DWM -> DWS -> MySQL -> BI
# 针对ODS层建库建表开发操作、建模
#
# 事实表：（看板2已有）customer_clue线索表
# 维度表：
# （看板2已有）customer_relationship意向客户表，主要为了判断数据来源为线上还是线下，也是意向客户指标的事实表。
# customer_appeal线索申诉表，主要为了判断线索数据是否有效。

HIVE_HOME=/usr/bin/hive
SQOOP_HOME=/usr/bin/sqoop

# 一、创建ODS的customer_appeal线索申诉表
${HIVE_HOME} -e "
CREATE EXTERNAL TABLE IF NOT EXISTS itcast_ods.customer_appeal (
  id int COMMENT 'customer_appeal_id',
  customer_relationship_first_id int COMMENT '第一条客户关系id',
  employee_id int COMMENT '申诉人',
  employee_name STRING COMMENT '申诉人姓名',
  employee_department_id int COMMENT '申诉人部门',
  employee_tdepart_id int COMMENT '申诉人所属部门',
  appeal_status int COMMENT '申诉状态，0:待稽核 1:无效 2：有效',
  audit_id int COMMENT '稽核人id',
  audit_name STRING COMMENT '稽核人姓名',
  audit_department_id int COMMENT '稽核人所在部门',
  audit_department_name STRING COMMENT '稽核人部门名称',
  audit_date_time STRING COMMENT '稽核时间',
  create_date_time STRING COMMENT '创建时间（申诉时间）',
  update_date_time STRING COMMENT '更新时间',
  deleted STRING COMMENT '删除标志位',
  tenant int COMMENT '租户id') 
comment '客户申诉表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY','orc.create.index'='true','orc.bloom.filter.columns'='appeal_status,customer_relationship_first_id');
"

# 二、使用Sqoop导入数据到ODS表中
${SQOOP_HOME} import \
--connect jdbc:mysql://192.168.10.150:3306/scrm \
--username root \
--password 123456 \
--query 'select id,customer_relationship_first_id,employee_id,employee_name,employee_department_id,employee_tdepart_id,appeal_status,audit_id,audit_name,audit_department_id,audit_department_name,audit_date_time,create_date_time,update_date_time,deleted,tenant,DATE_SUB(curdate(),interval 1 day) as start_time from customer_appeal where $CONDITIONS' \
--hcatalog-database itcast_ods \
--hcatalog-table customer_appeal \
-m 4 \
--split-by id
